package com.navi.core.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.navi.bean.sql.SqlVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.beans.PropertyVetoException;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;


@Slf4j
public class JdbcUtils {
    private static ComboPooledDataSource dataSource_MySql;

    private static ComboPooledDataSource dataSource_SqlServer;

    private static ComboPooledDataSource dataSource_PostgreSql;

    private static QueryRunner qr = new QueryRunner();


    static {
        try {
            Properties properties = new Properties();
            properties.load(JdbcUtils.class.getResourceAsStream("/application.properties"));
            String active_properties_name = properties.getProperty("spring.profiles.active");

            Properties active_properties = new Properties();
            active_properties.load(JdbcUtils.class.getResourceAsStream("/application-" + active_properties_name + ".properties"));

            //初始化MySql数据库连接池
            dataSource_MySql = new ComboPooledDataSource();
            dataSource_MySql.setInitialPoolSize(Integer.parseInt(active_properties.getProperty("spring.datasource.primary.initial-size")));
            dataSource_MySql.setMinPoolSize(Integer.parseInt(active_properties.getProperty("spring.datasource.primary.min-idle")));
            dataSource_MySql.setMaxPoolSize(Integer.parseInt(active_properties.getProperty("spring.datasource.primary.max-active")));
            dataSource_MySql.setMaxIdleTime(Integer.parseInt(active_properties.getProperty("spring.datasource.primary.max-wait")));
            dataSource_MySql.setUser(active_properties.getProperty("spring.datasource.primary.username"));
            dataSource_MySql.setPassword(active_properties.getProperty("spring.datasource.primary.password"));
            dataSource_MySql.setDriverClass(active_properties.getProperty("spring.datasource.primary.driverClassName"));
            dataSource_MySql.setJdbcUrl(active_properties.getProperty("spring.datasource.primary.url"));
            log.info("初始化MySql连接池成功......");

            //初始化SqlServer数据库连接池
            dataSource_SqlServer = new ComboPooledDataSource();
            dataSource_SqlServer.setInitialPoolSize(Integer.parseInt(active_properties.getProperty("spring.datasource.sqlServer.initialSize")));
            dataSource_SqlServer.setMinPoolSize(Integer.parseInt(active_properties.getProperty("spring.datasource.sqlServer.minIdle")));
            dataSource_SqlServer.setMaxPoolSize(Integer.parseInt(active_properties.getProperty("spring.datasource.sqlServer.maxActive")));
            dataSource_SqlServer.setUser(active_properties.getProperty("spring.datasource.sqlServer.username"));
            dataSource_SqlServer.setPassword(active_properties.getProperty("spring.datasource.sqlServer.password"));
            dataSource_SqlServer.setDriverClass(active_properties.getProperty("spring.datasource.sqlServer.driverClassName"));
            dataSource_SqlServer.setJdbcUrl(active_properties.getProperty("spring.datasource.sqlServer.url"));
            log.info("初始化SqlServer连接池成功......");

            ////初始化PostgreSql数据库连接池
            //dataSource_PostgreSql = new ComboPooledDataSource();
            //dataSource_PostgreSql.setInitialPoolSize(Integer.parseInt(active_properties.getProperty("spring.datasource.primary.initial-size")));
            //dataSource_PostgreSql.setMinPoolSize(Integer.parseInt(active_properties.getProperty("spring.datasource.primary.min-idle")));
            //dataSource_PostgreSql.setMaxPoolSize(Integer.parseInt(active_properties.getProperty("spring.datasource.primary.max-active")));
            //dataSource_PostgreSql.setUser(active_properties.getProperty("spring.datasource.primary.username"));
            //dataSource_PostgreSql.setPassword(active_properties.getProperty("spring.datasource.primary.password"));
            //dataSource_PostgreSql.setDriverClass(active_properties.getProperty("spring.datasource.primary.driverClassName"));
            //dataSource_PostgreSql.setJdbcUrl(active_properties.getProperty("spring.datasource.primary.url"));
            //log.info("初始化SqlServer连接池成功......");

        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }catch (FileNotFoundException ex){
            LoggerUtil.stackTraceToString(ex);
        }catch (IOException ex){
            LoggerUtil.stackTraceToString(ex);
        }
    }

    ////获取postgreSQL数据库连接线
    //public static Connection getGpConnection(){
    //    // 读取Properties配置文件
    //    try{
    //        Properties p1 = new Properties();
    //        p1.load(JdbcUtils.class.getResourceAsStream("/application.properties"));
    //        String propertiesName = p1.getProperty("spring.profiles.active");
    //
    //        Properties p2 = new Properties();
    //        p2.load(JdbcUtils.class.getResourceAsStream("/application-" + propertiesName + ".properties"));
    //        driverClassName = p2.getProperty("spring.datasource.secondary.driverClassName");
    //        url = p2.getProperty("spring.datasource.secondary.url");
    //        username = p2.getProperty("spring.datasource.secondary.username");
    //        password = p2.getProperty("spring.datasource.secondary.password");
    //    }catch (FileNotFoundException ex){
    //       LoggerUtil.stackTraceToString(ex);
    //    }catch (IOException ex){
    //        LoggerUtil.stackTraceToString(ex);
    //    }
    //
    //
    //    Connection conn = null;
    //    try {
    //        Class.forName(driverClassName);
    //        conn = DriverManager.getConnection(url, username, password);
    //    } catch (ClassNotFoundException e) {
    //        e.printStackTrace();
    //    } catch (SQLException e) {
    //        e.printStackTrace();
    //    }
    //    return conn;
    //}


    //获取MySQL数据库连接线
    public static Connection getMySQLConnection(){
        Connection conn = null;
        try {
            conn = dataSource_MySql.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //获取MySQL数据库连接线
    public static Connection getSqlServerConnection(){
        Connection conn = null;
        try {
            conn = dataSource_SqlServer.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }


    /**
     * 此方法是用来释放所有跟JDBC相关资源的
     * @param conn
     * @param rs
     * @param st
     */
    public static void free(Connection conn,ResultSet rs,Statement st){
        try {
            if(rs!=null){
                rs.close();
                rs = null;
            }
            if(st!=null){
                st.close();
                st = null;
            }
            if(conn!=null){
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }







    //MySql QueryRunner查询
    public static <T> List<T> mySql_QueryRunner_QueryListBySql(SqlVo sqlVo, final Class<T> clazz) {
        List<T> resultDataList = new ArrayList<>();
        try (Connection conn = JdbcUtils.getMySQLConnection()){
            long lBeginTimeTmp = System.currentTimeMillis();
            log.info("SQL: " + sqlVo.getSql());
            resultDataList = qr.query(conn, sqlVo.getSql(), new BeanListHandler<>(clazz));
            log.info("execution {}ms", (System.currentTimeMillis() - lBeginTimeTmp));
        } catch (Exception e) {
            log.error(LoggerUtil.stackTraceToString(e));
            e.printStackTrace();
            //throw e;
        }
        return resultDataList;
    }

    //Mysql QueryRunner Update
    public static int mySql_QueryRunner_Update(SqlVo sqlVo) throws Exception {
        int update_cnt = 0;
        try (Connection conn = JdbcUtils.getMySQLConnection()){
            long lBeginTimeTmp = System.currentTimeMillis();
            log.info("SQL: " + sqlVo.getSql());
            update_cnt = qr.update(conn, sqlVo.getSql());
            log.info("execution {}ms; update count {}", (System.currentTimeMillis() - lBeginTimeTmp), update_cnt);
        } catch (Exception e) {
            log.error(LoggerUtil.stackTraceToString(e));
            e.printStackTrace();
            throw e;
        }
        return update_cnt;
    }

    //SqlServer QueryRunner查询
    public static <T> List<T> sqlServer_QueryRunner_QueryListBySql(SqlVo sqlVo, final Class<T> clazz) {
        List<T> resultDataList = new ArrayList<>();
        try (Connection conn = JdbcUtils.getSqlServerConnection()){
            long lBeginTimeTmp = System.currentTimeMillis();
            log.info("SQL: " + sqlVo.getSql());
            resultDataList = qr.query(conn, sqlVo.getSql(), new BeanListHandler<>(clazz));
            log.info("execution {}ms", (System.currentTimeMillis() - lBeginTimeTmp));
        } catch (Exception e) {
            log.error(LoggerUtil.stackTraceToString(e));
            e.printStackTrace();
            //throw e;
        }
        return resultDataList;
    }
}
